package co.fitstart.mobile.dao.support;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.demo2do.core.persistence.GenericDaoSupport;
import com.demo2do.core.utils.CollectionUtils;
import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.entity.coach.CoachRestCalendar;
import co.fitstart.entity.coach.CoachRestType;
import co.fitstart.entity.coach.CoachStudentRelation;
import co.fitstart.entity.course.CourseType;
import co.fitstart.entity.course.round.CourseRound;
import co.fitstart.entity.course.schedule.CourseSchedule;
import co.fitstart.entity.course.schedule.CourseScheduleCancelRecord;
import co.fitstart.entity.course.schedule.ScheduleSmsNotice;
import co.fitstart.entity.course.schedule.ScheduleStatus;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.site.RoomType;
import co.fitstart.entity.site.Site;
import co.fitstart.entity.user.Student;
import co.fitstart.mobile.entity.appointment.CoachRestPrepare;
import co.fitstart.mobile.entity.appointment.RestCalendarStat;
import co.fitstart.mobile.entity.appointment.RoomScheduleStat;

/**
 * 
 * @author mohanlan
 *
 */
@Repository("scheduleDaoSupport")
public class ScheduleDaoSupport {
    
    @Autowired
    private GenericDaoSupport genericDaoSupport;
    
    /**
     * 
     * @param coach
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public Map<String, CoachRestCalendar> mapCoachResrCalendar(Principal coach, Date day) {
        
        String querySentence = "FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coach = :coach AND coachRestCalendar.restDate = :restDate";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("restDate", day);
        
        List<CoachRestCalendar> coachRestCalendars = genericDaoSupport.searchForList(querySentence, parameters);
        
        Map<String, CoachRestCalendar> restCalendars = new HashMap<String, CoachRestCalendar>();
        Map<String, List<CoachRestCalendar>> calendars = CollectionUtils.constructMapValuesFromList(coachRestCalendars, "dayhour");
        for (String key : calendars.keySet()) {
            if(calendars.get(key).size() > 1) {
                for(CoachRestCalendar calendar : calendars.get(key)) {
                    if(!calendar.getCoachRestType().isDuty()) {
                        restCalendars.put(key, calendar);
                    }
                }
            } else {
                restCalendars.put(key, calendars.get(key).get(0));
            }
        }
        
        return restCalendars;
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @param hours
     * @return
     */
    public CoachRestCalendar loadCoachRestCalendar(Principal coach, Date day, String[] hours) {
        
        String querySentence = "FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coach = :coach AND coachRestType != :coachRestType AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.restHour in (:restHour)";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("coachRestType", CoachRestType.DUTY);
        parameters.put("restDate", day);
        parameters.put("restHour", hours);
        
        return genericDaoSupport.searchForObject(querySentence, parameters, CoachRestCalendar.class);
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @param hours
     * @param excludedScheduleId
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(Principal coach, Date day, String[] hours, Long excludedScheduleId) {
        StringBuffer querySentence = new StringBuffer("FROM CourseSchedule WHERE disabled = false"
                + " AND coach=:coach AND occurDate=:occurDate AND occurHour in (:occurHours)");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("occurDate", day);
        parameters.put("occurHours", hours);
        
        if(excludedScheduleId != null) {
            querySentence.append(" AND id!=:scheduleId");
            
            parameters.put("scheduleId", excludedScheduleId);
        }
        
        return genericDaoSupport.searchForList(querySentence.toString(), parameters);
    }
    
    /**
     * 
     * @param courseRound
     * @param student
     * @param day
     * @return
     */
    public CourseSchedule loadCourseSchedule(CourseRound courseRound, Student student, Date day) {
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND student = :student AND occurDate = :occurDate AND courseRound = :courseRound";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("student", student);
        parameters.put("occurDate", day);
        parameters.put("courseRound", courseRound);
        
        return genericDaoSupport.searchForObject(querySentence, parameters, CourseSchedule.class);
    }
    

    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    public CourseSchedule loadCourseSchedule(CourseRound courseRound, ScheduleStatus scheduleStatus) {
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND scheduleStatus = :scheduleStatus AND courseRound = :courseRound";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("scheduleStatus", scheduleStatus);
        parameters.put("courseRound", courseRound);
        
        return genericDaoSupport.searchForObject(querySentence, parameters, CourseSchedule.class);
    }
    
    /**
     * 
     * @param student
     * @param day
     * @param hours
     * @param experience
     * @param excludedScheduleId
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listUnfinishedCourseSchedule(Student student, Date day,
            String[] hours, boolean experience, Long excludedScheduleId) {
        StringBuffer querySentence = new StringBuffer("FROM CourseSchedule WHERE disabled = false AND scheduleStatus!=:finish"
                + " AND student=:student AND occurDate=:occurDate AND occurHour in (:occurHours)"
                + " AND course.experience=:experience");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("finish", ScheduleStatus.FINISH);
        parameters.put("student", student);
        parameters.put("occurDate", day);
        parameters.put("occurHours", hours);
        parameters.put("experience", experience);
        
        if(excludedScheduleId != null) {
            querySentence.append(" AND id!=:scheduleId");
            
            parameters.put("scheduleId", excludedScheduleId);
        }
        
        return genericDaoSupport.searchForList(querySentence.toString(), parameters);
    }
    
    /**
     * 
     * @param coachId
     * @return
     */
    public Principal loadCoach(Long coachId) {
        return genericDaoSupport.load(Principal.class, coachId);
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public Map<String, CourseSchedule> mapCourseSchedule(Principal coach, Date day) {
        
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND coach = :coach AND scheduleStatus =:scheduleStatus AND occurDate = :occurDate";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("occurDate", day);
        parameters.put("scheduleStatus", ScheduleStatus.READY);
        
        List<CourseSchedule> courseSchedules = genericDaoSupport.searchForList(querySentence, parameters);
        
        return CollectionUtils.constructMapValueFromList(courseSchedules, "dayhour");
    }
    
    /**
     * 
     * @param student
     * @param scheduleStatus
     * @param courseRound
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(Student student, ScheduleStatus scheduleStatus, CourseRound courseRound) {
        
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND student = :student AND scheduleStatus =:scheduleStatus";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("courseRound", courseRound);
        parameters.put("student", student);
        parameters.put("scheduleStatus", scheduleStatus);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * 
     * @param student
     * @param courseRound
     * @param startDate
     * @param endDate
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(Student student, CourseRound courseRound, Date startDate, Date endDate) {
        
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND student = :student AND occurDate >= :startDate AND occurDate <= :endDate";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("student", student);
        parameters.put("courseRound", courseRound);
        parameters.put("startDate", startDate);
        parameters.put("endDate", endDate);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * @param coach
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(Principal coach, Date day) {
        
        String querySentence = "FROM CourseSchedule WHERE coach = :coach AND occurDate = :occurDate AND disabled = false";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("occurDate", day);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * 
     * @param student
     * @param courseRound
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(Student student, CourseRound courseRound) {
        
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND student = :student ORDER BY occurDate DESC";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("student", student);
        parameters.put("courseRound", courseRound);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * @param student
     * @param courseRound
     * @return
     */
    public int count(Student student, CourseRound courseRound) {
        
        String queryString = "SELECT COUNT(1) FROM CourseSchedule WHERE disabled = false AND student = :student AND courseRound = :courseRound AND occurDate IS NOT NULL AND occurHour IS NOT NULL";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("student", student);
        parameters.put("courseRound", courseRound);
        
        return genericDaoSupport.searchForInt(queryString, parameters);
        
    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @return
     */
//    @SuppressWarnings("unchecked")
//    public Map<String, Integer> dayhourRoomLimits(Site site, RoomType roomType, Date day) {
//        
//        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.RoomScheduleStat(occurDate, occurHour, SUM(amount)) FROM  RoomScheduleRecord WHERE disabled = false AND roomType = :roomType AND site = :site AND occurDate = :occurDate GROUP BY occurDate, occurHour";
//        
//        Map<String, Object> parameters = new HashMap<String, Object>();
//        parameters.put("roomType", roomType);
//        parameters.put("site", site);
//        parameters.put("occurDate", day);
//        
//        List<RoomScheduleStat> roomScheduleStats = genericDaoSupport.searchForList(querySentence, parameters);
//        
//        Map<String, Integer> result = new HashMap<String, Integer>();
//        for(RoomScheduleStat roomScheduleStat : roomScheduleStats) {
//            result.put(roomScheduleStat.getDayhour(), roomScheduleStat.getAmount());
//        }
//        
//        return result;
//        
//    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param appointStartTime
     * @param appointEndTime
     * @param experience
     * @return
     */
    @SuppressWarnings({ "unchecked", "unlikely-arg-type" })
    public Map<String, Integer> dayhourRoomLimits(Site site, RoomType roomType, Date day, String[] appointStartTime, String[] appointEndTime, boolean experience) {
        List<String> appointHours = this.getAppointHours(appointStartTime, appointEndTime);
        
        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.RoomScheduleStat(schedule.occurDate, schedule.occurHour, SUM(schedule.roomSchedule.amount)) FROM  CourseSchedule schedule WHERE schedule.disabled = false AND schedule.site = :site AND schedule.roomSchedule.roomType = :roomType AND schedule.occurDate = :occurDate GROUP BY schedule.occurDate, schedule.occurHour";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("roomType", roomType);
        parameters.put("site", site);
        parameters.put("occurDate", day);
        
        List<RoomScheduleStat> roomScheduleStats = genericDaoSupport.searchForList(querySentence, parameters);
        
        Map<String, Integer> experienceStats = this.listExperienceScheduleStat(site, roomType, day);
        
        Map<String, Integer> result = new HashMap<String, Integer>();
        Map<String, RoomScheduleStat> stats = CollectionUtils.constructMapValueFromList(roomScheduleStats, "hour");
        for(String appointHour : appointHours) {
            int amount = 0;
            
            Date currentDay = DateUtils.parseDate(DateFormatUtils.format(new Date(), "yyyy-MM-dd ") + appointHour + ":00", "yyyy-MM-dd HH:mm:ss");
            String beforeHalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, -30), "HH:mm");
            String afterHalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, 30), "HH:mm");
            
            if(stats.containsKey(appointHour)) {
                amount = amount + stats.get(appointHour).getAmount();
            }
            
            int beforeHalfhourAmount = 0;
            int afterHalfhourAmount = 0;
            if(stats.containsKey(beforeHalfhour)) {
                beforeHalfhourAmount = stats.get(beforeHalfhour).getAmount();
            }
            if(stats.containsKey(afterHalfhour)) {
                afterHalfhourAmount = stats.get(afterHalfhour).getAmount();
            }
            if(experienceStats.containsKey(appointHour)) {
                beforeHalfhourAmount = beforeHalfhourAmount + experienceStats.get(appointHour);
            }
            if(experienceStats.containsKey(afterHalfhourAmount)) {
                afterHalfhourAmount = afterHalfhourAmount + experienceStats.get(afterHalfhourAmount);
            }
            
            amount = amount + (beforeHalfhourAmount < afterHalfhourAmount ? afterHalfhourAmount : beforeHalfhourAmount);
            
            if(experience) {
                String afterHour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addHours(currentDay, 1), "HH:mm");
                int afterHourAmount = 0;
                
                if(stats.containsKey(afterHalfhour)) {
                    afterHourAmount = stats.get(afterHalfhour).getAmount();
                }
                if(stats.containsKey(afterHour)) {
                    afterHourAmount = afterHourAmount + stats.get(afterHour).getAmount();
                }
                if(experienceStats.containsKey(afterHour)) {
                    afterHourAmount = afterHourAmount + experienceStats.get(afterHour);
                }
                
                amount = (amount < afterHourAmount ? afterHourAmount : amount);
            }
            
            result.put(DateFormatUtils.format(day, "yyyy-MM-dd|") + appointHour, amount);
        }
        
        return result;
        
    }
    
    /**
     * 
     * @param site
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public Map<String, Integer> dayhourScheduleCoachAmount(Site site, Date day) {
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND site = :site AND occurDate = :occurDate";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("site", site);
        parameters.put("occurDate", day);
        
        List<CourseSchedule> courseSchedules = genericDaoSupport.searchForList(querySentence, parameters);
        Map<String, List<CourseSchedule>> schedules = CollectionUtils.constructMapValuesFromList(courseSchedules, "occurHour");
        
        Map<String, Integer> result = new HashMap<String, Integer>();
        for(String key : schedules.keySet()) {
            
            Date currentDay = DateUtils.parseDate(DateFormatUtils.format(new Date(), "yyyy-MM-dd ") + key + ":00", "yyyy-MM-dd HH:mm:ss");
            String beforeHalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, -30), "HH:mm");
            String afterHalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, 30), "HH:mm");
            
            Set<CourseSchedule> dinstinctSchedules = new HashSet<CourseSchedule>();
            
            if(schedules.get(beforeHalfhour) != null) {
                List<CourseSchedule> beforeSchedules = schedules.get(DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, -30), "HH:mm"));
                if(beforeSchedules != null) {
                    dinstinctSchedules.addAll(beforeSchedules);
                }
            } else {
                result.put(DateFormatUtils.format(day, "yyyy-MM-dd") + "|" + beforeHalfhour, schedules.get(key).size());
            }
            
            if(schedules.get(afterHalfhour) != null) {
                List<CourseSchedule> afterSchedules = schedules.get(DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, -30), "HH:mm"));
                if(afterSchedules != null) {
                    dinstinctSchedules.addAll(afterSchedules);
                }
            } else {
                result.put(DateFormatUtils.format(day, "yyyy-MM-dd") + "|" + afterHalfhour, schedules.get(key).size());
            }
            
            result.put(DateFormatUtils.format(day, "yyyy-MM-dd") + "|" + key, schedules.get(key).size() + dinstinctSchedules.size());
        }
        
        return result;
    }
    
    /**
     * 
     * @param site
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public Map<String, Integer> dayhourRestCoachAmount(Site site, Date day) {
        List<Principal> coaches = this.listCoach(site, day);
        
        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.RestCalendarStat(restDate, restHour, COUNT(*)) FROM CoachRestCalendar WHERE disabled = false AND coach in (:coaches) AND restDate = :restDate AND coachRestType != 2 GROUP BY restDate, restHour";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coaches", coaches);
        parameters.put("restDate", day);
        
        List<RestCalendarStat> restCalendarStats = genericDaoSupport.searchForList(querySentence, parameters);
        
        Map<String, Integer> result = new HashMap<String, Integer>();
        for(RestCalendarStat restCalendarStat : restCalendarStats) {
            result.put(restCalendarStat.getDayhour(), restCalendarStat.getAmount());
        }
        
        return result;
    }
    
    /**
     * 
     * @param site
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public Map<String, Integer> dayhourRemainCoach(Site site, Date day, String[] appointStartTime, String[] appointEndTime, boolean experience) {
        
        Map<String, Integer> result = new HashMap<String, Integer>();
        
        List<String> appointHours = this.getAppointHours(appointStartTime, appointEndTime);
        
        // 1. get this day's courseSchedule - key:occurHour
        Map<String, List<CourseSchedule>> schedules = this.dayhourSchedule(site, day);
        
        // 2. get this day's rest coach - key:restHour
        List<Principal> coaches = this.listCoach(site, day);
        Map<String, List<CoachRestCalendar>> calendars = this.dayhourCalendar(coaches, day);
        
        // 3. calculate hour's remain coach amount
        int totalCoach = coaches.size();
        for(String appointHour : appointHours) {
            int remainCoach = totalCoach;
            Date currentDay = DateUtils.parseDate(DateFormatUtils.format(new Date(), "yyyy-MM-dd ") + appointHour + ":00", "yyyy-MM-dd HH:mm:ss");
            String beforeHalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, -30), "HH:mm");
            String afterHalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, 30), "HH:mm");
            String afterAnHour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDay, 60), "HH:mm");
            
            Set<Long> restCoaches = new HashSet<Long>();
            // 列出所有该时间及前后半个小时及后一小时上课的教练
            if(schedules.containsKey(appointHour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(schedules.get(appointHour), "coach.id"));
            }
            if(schedules.containsKey(beforeHalfhour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(schedules.get(beforeHalfhour), "coach.id"));
            }
            if(schedules.containsKey(afterHalfhour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(schedules.get(afterHalfhour), "coach.id"));
            }
            if(experience && schedules.containsKey(afterAnHour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(schedules.get(afterAnHour), "coach.id"));
            }
            
            // 列出所有该时间、后半个小时后一个小时休息的教练，与上课教练distinct
            if(calendars.containsKey(appointHour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(calendars.get(appointHour), "coach.id"));
            }
            if(calendars.containsKey(afterHalfhour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(calendars.get(afterHalfhour), "coach.id"));
            }
            if(experience && calendars.containsKey(afterAnHour)) {
                restCoaches.addAll(CollectionUtils.extractPropertyList(calendars.get(afterAnHour), "coach.id"));
            }
            
            remainCoach = remainCoach - restCoaches.size();
            result.put(DateFormatUtils.format(day, "yyyy-MM-dd") + "|" + appointHour, remainCoach);
        }
        
        return result;
    }
    
    /**
     * 
     * @param appointStartTime
     * @param appointEndTime
     * @return
     */
    private List<String> getAppointHours(String[] appointStartTime, String[] appointEndTime) {
        Date startDate = DateUtils.parseDate(DateFormatUtils.format(new Date(), "yyyy-MM-dd ") + appointStartTime[0] + ":" + appointStartTime[1], "yyyy-MM-dd HH:mm");
        Date endDate = DateUtils.parseDate(DateFormatUtils.format(new Date(), "yyyy-MM-dd ") + appointEndTime[0] + ":" + appointEndTime[1], "yyyy-MM-dd HH:mm");
        
        List<String> times = new ArrayList<String>();
        
        while(startDate.getTime() <  org.apache.commons.lang3.time.DateUtils.addMinutes(endDate, -30).getTime()) {
            times.add(DateFormatUtils.format(startDate, "HH:mm"));
            startDate = org.apache.commons.lang3.time.DateUtils.addMinutes(startDate, 30);
        }
        return times;
    
    }
    
    /**
     * 
     * @param site
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    private Map<String, List<CourseSchedule>> dayhourSchedule(Site site, Date day) {
        String querySentence = "FROM CourseSchedule WHERE disabled = false AND site = :site AND occurDate = :occurDate";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("site", site);
        parameters.put("occurDate", day);
        
        List<CourseSchedule> courseSchedules = genericDaoSupport.searchForList(querySentence, parameters);
        return CollectionUtils.constructMapValuesFromList(courseSchedules, "occurHour");
    }
    
    /**
     * 
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    private Map<String, List<CoachRestCalendar>> dayhourCalendar(List<Principal> coaches, Date day) {
        if(coaches.isEmpty()) {
            return new HashMap<>();
        }
        String querySentence = "FROM CoachRestCalendar WHERE disabled = false AND coach in (:coaches) AND restDate = :restDate AND coachRestType != 2";
        
        Map<String, Object> restParameters = new HashMap<String, Object>();
        restParameters.put("coaches", coaches);
        restParameters.put("restDate", day);
        
        List<CoachRestCalendar> restCalendars = genericDaoSupport.searchForList(querySentence, restParameters);
        return CollectionUtils.constructMapValuesFromList(restCalendars, "restHour");
    }
    
    /**
     * 
     * @param site
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Principal> listCoach(Site site, Date day) {
        int dayOfWeek = getDayOfWeek(day);
        
        String querySentence = "SELECT DISTINCT(principalReportRelation.coach) FROM PrincipalReportRelation principalReportRelation"
                + " WHERE principalReportRelation.coach IS NOT NULL"
                + " AND principalReportRelation.manager IS NOT NULL"
                + " AND principalReportRelation.site = :site"
                + " AND (principalReportRelation.appointDays IS NULL OR principalReportRelation.appointDays LIKE :dayOfWeek)";
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("site", site);
        parameters.put("dayOfWeek", "%" + dayOfWeek + ",%");
        
        List<Principal> coaches = genericDaoSupport.searchForList(querySentence, parameters);
        
        List<Principal> results = new ArrayList<Principal>();
        
        for(Principal coach: coaches) {
            if(!coach.isDisabled()) {
                results.add(coach);
            }
        }
        
        return results;
    }
    
    /**
     * 
     * @param day
     * @param hours
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Long> listOnGoingCoachIds(Date day, List<String> hours) {
        
        String querySentence = "SELECT coach.id FROM CourseSchedule WHERE disabled = false AND coach IS NOT NULL AND occurDate = :day AND occurHour in(:hours)";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("day", day);
        parameters.put("hours", hours);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * 
     * @param day
     * @param hour
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CoachRestPrepare> listRestCoachs(Date day, String hour) {
        
        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.CoachRestPrepare(coach.id, coachRestType) FROM CoachRestCalendar WHERE disabled = false AND coach IS NOT NULL AND coachRestType != :coachRestType AND restDate = :day AND restHour = :hour";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coachRestType", CoachRestType.DUTY);
        parameters.put("day", day);
        parameters.put("hour", hour);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * 
     * @param day
     * @param hour
     * @return
     */
//    @SuppressWarnings("unchecked")
//    public List<CoachRestPrepare> listDutyCoachs(Date day, String hour) {
//        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.CoachRestPrepare(coach.id, coachRestType) FROM CoachRestCalendar WHERE disabled = false AND coach IS NOT NULL AND restDate = :day AND coachRestType = 2 AND restHour = :hour";
//        
//        Map<String, Object> parameters = new HashMap<String, Object>();
//        parameters.put("day", day);
//        parameters.put("hour", hour);
//        
//        return genericDaoSupport.searchForList(querySentence, parameters);
//    }
    
    /**
     * 
     * @param hour
     * @param experience
     * @return
     */
    public List<String> getScheduleObligatoryHours(String hour, boolean experience) {
        List<String> hours = new ArrayList<String>();
        
        Date day = DateUtils.parseDate(DateFormatUtils.format(new Date(), "yyyy-MM-dd") + " " + hour + ":00", "yyyy-MM-dd HH:mm:ss");
        
        hours.add(hour);
        hours.add(DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(day, -30), "HH:mm"));
        hours.add(DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(day, 30), "HH:mm"));
        if(experience) {
            hours.add(DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addHours(day, 1), "HH:mm"));
        }
        
        return hours;
    }
    
    /**
     * 
     * @param day
     * @param hour
     * @param experience
     * @return
     */
    public Map<String, List<Long>> filterRestCoachIds(Date day, String hour, boolean experience) {
    	
        List<CoachRestPrepare> restHours = new ArrayList<CoachRestPrepare>();
        List<CoachRestPrepare> otherCoachRestHours = new ArrayList<CoachRestPrepare>();
        List<CoachRestPrepare> afterAnHourRestHours = new ArrayList<CoachRestPrepare>();
        restHours.addAll(this.listRestCoachs(day, hour));
//        restHours.addAll(this.listDutyCoachs(day, hour));
        Date date = DateUtils.parseDate(DateFormatUtils.format(day, "yyyy-MM-dd") + " " + hour + ":00", "yyyy-MM-dd HH:mm:ss");
        otherCoachRestHours = this.listRestCoachs(day, DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(date, 30), "HH:mm"));
        
        for(CoachRestPrepare coachRestPrepare : otherCoachRestHours) {
            boolean flag = false;
            for (CoachRestPrepare restPrepare : restHours) {
                if(coachRestPrepare.getCoachId() == restPrepare.getCoachId()) {
                    flag = true;
                }
            }
            if(!flag) {
                restHours.add(coachRestPrepare);
            }
        }
        
        if(experience) {
            afterAnHourRestHours = this.listRestCoachs(day, DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addHours(date, 1), "HH:mm"));
            
            for(CoachRestPrepare coachRestPrepare : afterAnHourRestHours) {
                boolean flag = false;
                for (CoachRestPrepare restPrepare : restHours) {
                    if(coachRestPrepare.getCoachId() == restPrepare.getCoachId()) {
                        flag = true;
                    }
                }
                if(!flag) {
                    restHours.add(coachRestPrepare);
                }
            }
        }
        
        Map<String, List<CoachRestPrepare>> result = CollectionUtils.constructMapValuesFromList(restHours, "coachRestType.alias");
        Map<String, List<Long>> restCoachIds = new HashMap<String, List<Long>>();
        for(String key : result.keySet()) {
            List<Long> coachIds = new ArrayList<Long>();
            for(CoachRestPrepare value : result.get(key)) {
                coachIds.add(value.getCoachId());
            }
            restCoachIds.put(key, coachIds);
        }
        
        return restCoachIds;
    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param hour
     * @return
     */
//    @SuppressWarnings("unchecked")
//    public RoomScheduleStat loadRoomScheduleStat(Site site, RoomType roomType, Date day, String hour) {
//        
//        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.RoomScheduleStat(occurDate, occurHour, SUM(amount)) FROM RoomScheduleRecord WHERE disabled = false AND roomType = :roomType AND site = :site AND occurDate = :occurDate AND occurHour = :occurHour";
//        
//        Map<String, Object> parameters = new HashMap<String, Object>();
//        parameters.put("roomType", roomType);
//        parameters.put("site", site);
//        parameters.put("occurDate", day);
//        parameters.put("occurHour", hour);
//        
//        List<RoomScheduleStat> roomScheduleStats = genericDaoSupport.searchForList(querySentence, parameters);
//        
//        return roomScheduleStats.size() > 0 ? roomScheduleStats.get(0) : null;
//    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param hour
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(Site site, RoomType roomType, Date day, String hour) {
        Date currentDate = DateUtils.parseDate(DateFormatUtils.format(day, "yyyy-MM-dd ") + hour, "yyyy-MM-dd HH:mm");
        String beforehalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDate, -30), "HH:mm");
        String afterhalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDate, 30), "HH:mm");
        
        String querySentence = "FROM CourseSchedule schedule WHERE schedule.disabled = false"
                + " AND schedule.roomType = :roomType AND schedule.site = :site"
                + " AND schedule.occurDate = :occurDate AND schedule.occurHour in (:occurHour)";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("roomType", roomType);
        parameters.put("site", site);
        parameters.put("occurDate", day);
        parameters.put("occurHour", new String[]{hour, beforehalfhour, afterhalfhour});
        
        return genericDaoSupport.searchForList(querySentence, parameters);
        
    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param hour
     * @param experience
     * @return
     */
    public int countUsingRoom(Site site, RoomType roomType, Date day, String hour, boolean experience) {
        Date currentDate = DateUtils.parseDate(DateFormatUtils.format(day, "yyyy-MM-dd ") + hour, "yyyy-MM-dd HH:mm");
        
        String beforeHour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addHours(currentDate, -1), "HH:mm");
        String beforehalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDate, -30), "HH:mm");
        String afterhalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDate, 30), "HH:mm");
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("roomType", roomType);
        parameters.put("site", site);
        parameters.put("occurDate", day);
        
        parameters.put("occurHour", hour);
        int currentRoomCount = genericDaoSupport.count("FROM CourseSchedule"
                + " WHERE disabled = false AND roomType = :roomType AND site = :site"
                + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
        
        parameters.put("occurHour", beforehalfhour);
        int beforeRoomCount = genericDaoSupport.count("FROM CourseSchedule"
                + " WHERE disabled = false AND roomType = :roomType AND site = :site"
                + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
        
        parameters.put("occurHour", afterhalfhour);
        int afterRoomCount = genericDaoSupport.count("FROM CourseSchedule"
                + " WHERE disabled = false AND roomType = :roomType AND site = :site"
                + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
        
        //若一小时前有1V1体验课，则当前时间至半小时后仍会占用房间
        parameters.put("individual", CourseType.INDIVIDUAL);
        parameters.put("occurHour", beforeHour);
        beforeRoomCount = beforeRoomCount + genericDaoSupport.count("FROM CourseSchedule"
                + " WHERE disabled = false AND courseType=:individual AND course.experience=true"
                + " AND roomType = :roomType AND site = :site"
                + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
        
        //若半小时前有1V1体验课，则半小时后至一小时后仍会占用房间
        parameters.put("occurHour", beforehalfhour);
        afterRoomCount = afterRoomCount + genericDaoSupport.count("FROM CourseSchedule"
                + " WHERE disabled = false AND courseType=:individual AND course.experience=true"
                + " AND roomType = :roomType AND site = :site"
                + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
        
        int result = currentRoomCount + (beforeRoomCount < afterRoomCount ? afterRoomCount : beforeRoomCount);
        
        if(experience) {
            String afterHour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addHours(currentDate, 1), "HH:mm");
            parameters.put("occurHour", afterHour);
            int afterHourRoomCount = genericDaoSupport.count("FROM CourseSchedule"
                    + " WHERE disabled = false AND roomType = :roomType AND site = :site"
                    + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
            
            //若当前时间有1V1体验课，则一小时后至一个半小时后仍会占用房间
            parameters.put("occurHour", hour);
            afterHourRoomCount = afterHourRoomCount + genericDaoSupport.count("FROM CourseSchedule"
                    + " WHERE disabled = false AND courseType=:individual AND course.experience=true"
                    + " AND roomType = :roomType AND site = :site"
                    + " AND occurDate = :occurDate AND occurHour = :occurHour", parameters);
            
            if((afterHourRoomCount + afterRoomCount) > result) {
                result = afterHourRoomCount + afterRoomCount;
            }
        }
        
        return result;
    }
    
    /**
     * 
     * @param coaches
     * @param day
     * @param hour
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Principal> listDayhourRestCoach(List<Principal> coaches, Date day, String hour) {
        Date currentDate = DateUtils.parseDate(DateFormatUtils.format(day, "yyyy-MM-dd ") + hour, "yyyy-MM-dd HH:mm");
        String afterhalfhour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addMinutes(currentDate, 30), "HH:mm");
        
        String querySentence = "SELECT DISTINCT(rest.coach) FROM CoachRestCalendar rest WHERE rest.disabled = false AND rest.coachRestType != 2 AND rest.coach in ( :coaches ) AND rest.restDate = :day AND rest.restHour in ( :hours )";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coaches", coaches);
        parameters.put("day", day);
        parameters.put("hours", new String[]{hour, afterhalfhour});
        
        return genericDaoSupport.searchForList(querySentence, parameters);
        
    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param hours
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<RoomScheduleStat> loadRoomScheduleStat(Site site, RoomType roomType, Date day, String[] hours) {
        
        String querySentence = "SELECT new co.fitstart.mobile.entity.appointment.RoomScheduleStat(occurDate, occurHour, SUM(amount)) FROM RoomScheduleRecord WHERE disabled = false AND roomType = :roomType AND site = :site AND occurDate = :occurDate AND occurHour in (:occurHour) GROUP BY occurDate, occurHour";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("roomType", roomType);
        parameters.put("site", site);
        parameters.put("occurDate", day);
        parameters.put("occurHour", hours);
        
        return genericDaoSupport.searchForList(querySentence, parameters);
    }
    
    /**
     * 
     * @param coach
     * @param student
     * @param courseType
     * @return
     */
    public CoachStudentRelation loadCoachStudentRelation(Principal coach, Student student, CourseType courseType) {
        
        String querySentence = "FROM CoachStudentRelation WHERE coach = :coach AND student = :student AND courseType = :courseType";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("student", student);
        parameters.put("courseType", courseType);
        
        return genericDaoSupport.searchForObject(querySentence, parameters, CoachStudentRelation.class);
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<Site> listSite(Principal coach, Date day) {
        int dayOfWeek = getDayOfWeek(day);
        
        String querySentence = "SELECT DISTINCT(principalReportRelation.site) FROM PrincipalReportRelation principalReportRelation"
                + " WHERE principalReportRelation.coach = :coach"
                + " AND principalReportRelation.manager IS NOT NULL"
                + " AND principalReportRelation.site IS NOT NULL"
                + " AND (principalReportRelation.appointDays IS NULL OR principalReportRelation.appointDays LIKE :dayOfWeek)";
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("coach", coach);
        parameters.put("dayOfWeek", "%" + dayOfWeek + ",%");
        
        List<Site> sites = genericDaoSupport.searchForList(querySentence, parameters);
        
        List<Site> results = new ArrayList<Site>();
        
        for(Site site: sites) {
            if(!site.isDisabled()) {
                results.add(site);
            }
        }
        
        return results;
    }
    
    /**
     * 
     * @param courseSchedule
     */
    public void createScheduleSmsNotice(CourseSchedule courseSchedule) {
        if((courseSchedule.getCourseType().isIndividual() || courseSchedule.getCourseType().isYear()) && !courseSchedule.getCourse().isExperience()) {
            genericDaoSupport.executeHQL("UPDATE ScheduleSmsNotice SET disabled = true where courseSchedule = :courseSchedule", "courseSchedule", courseSchedule);
            
            ScheduleSmsNotice notice = new ScheduleSmsNotice(null, courseSchedule);
            
            genericDaoSupport.save(notice);
        }
    }
    
    /**
     * 
     * @param courseSchedule
     */
    public void disableScheduleSmsNotice(CourseSchedule courseSchedule) {
        ScheduleSmsNotice notice = genericDaoSupport.searchForObject(
                "FROM ScheduleSmsNotice WHERE disabled = false"
                + " AND courseSchedule = :courseSchedule", "courseSchedule",
                courseSchedule, ScheduleSmsNotice.class);
        
        if(notice != null) {
            genericDaoSupport.update(notice.disable());
        }
    }
    
    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<CourseSchedule> listCourseSchedule(CourseRound courseRound, ScheduleStatus[] scheduleStatus) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseRound", courseRound);
        parameters.put("scheduleStatus", scheduleStatus);
        
        return genericDaoSupport.searchForList("FROM CourseSchedule WHERE disabled = false"
                + " AND courseRound = :courseRound AND scheduleStatus in ( :scheduleStatus)"
                + " ORDER BY occurDate ASC,occurHour ASC", parameters);
    }
    
    /**
     * 
     * @param courseSchedule
     * @return
     */
    public int getRank(CourseSchedule courseSchedule) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseRound", courseSchedule.getCourseRound());
        parameters.put("scheduleStatus", new ScheduleStatus[]{ScheduleStatus.READY, ScheduleStatus.FINISH, ScheduleStatus.EXPIRY});
        parameters.put("occurDate", courseSchedule.getOccurDate());
        parameters.put("occurHour", courseSchedule.getOccurHour());
        
        return genericDaoSupport.count("FROM CourseSchedule WHERE disabled=false"
                + " AND courseRound=:courseRound AND scheduleStatus IN (:scheduleStatus)"
                + " AND (occurDate<:occurDate OR (occurDate=:occurDate AND occurHour<=:occurHour))", parameters);
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @param hour
     * @param coachRestType
     * @return
     */
    public CoachRestCalendar loadCoachRestCalendar(Principal coach, Date day, String hour, CoachRestType coachRestType) {
        String querySentence = "FROM CoachRestCalendar WHERE disabled = false"
                + " AND coach=:coach AND coachRestType=:coachRestType"
                + " AND restDate=:restDate AND restHour=:restHour";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("coachRestType", coachRestType);
        parameters.put("restDate", day);
        parameters.put("restHour", hour);
        
        return genericDaoSupport.searchForObject(querySentence, parameters, CoachRestCalendar.class);
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @param hour
     * @param coachRestType
     */
    public void createCoachRestCalendar(Principal coach, Date day, String hour, CoachRestType coachRestType) {
        Date dayhour = DateUtils.parseDate(DateFormatUtils.format(day, "yyyy-MM-dd ") + hour, "yyyy-MM-dd HH:mm");
        String restHour = DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addHours(dayhour, 1), "HH:mm");
        
        CoachRestCalendar calendar = new CoachRestCalendar(coach, coachRestType, day, restHour, false);
        genericDaoSupport.save(calendar);
    }
    
    /**
     * 
     * @param coach
     * @param day
     * @param hour
     * @return
     */
    public CourseSchedule loadCourseSchedule(Principal coach, Date day, String hour) {
        String querySentence = "FROM CourseSchedule WHERE disabled=false"
                + " AND coach=:coach AND occurDate=:occurDate"
                + " AND occurHour=:occurHour";
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("coach", coach);
        parameters.put("occurDate", day);
        parameters.put("occurHour", hour);
        
        return genericDaoSupport.searchForObject(querySentence, parameters, CourseSchedule.class);
    }
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param experience
     * @return
     */
    @SuppressWarnings("unchecked")
    private Map<String, Integer> listExperienceScheduleStat(Site site, RoomType roomType, Date day) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("site", site);
        parameters.put("individual", CourseType.INDIVIDUAL);
        parameters.put("roomType", roomType);
        parameters.put("occurDate", day);
        parameters.put("experience", true);
        
        List<CourseSchedule> schedules = genericDaoSupport.searchForList("FROM CourseSchedule WHERE disabled = false"
                + " AND site=:site AND courseType=:individual AND roomType=:roomType AND occurDate=:occurDate AND course.experience=:experience"
                + " ORDER BY occurHour ASC", parameters);
        
        Map<String, Integer> result = new HashMap<>();
        
        if(!schedules.isEmpty()) {
            String hour = schedules.get(0).getOccurHour();
            String afterHour = (Integer.parseInt(hour.split(":")[0]) + 1) + ":" + hour.split(":")[1];
            int amount = 0;
            
            for(CourseSchedule courseSchedule : schedules) {
                if(!courseSchedule.getOccurHour().equals(hour)) {
                    result.put(afterHour, amount);
                    
                    hour = courseSchedule.getOccurHour();
                    afterHour = (Integer.parseInt(hour.split(":")[0]) + 1) + ":" + hour.split(":")[1];
                    amount = 0;
                } else {
                    amount++;
                }
            }
            
            result.put(afterHour, amount);
        }
        
        return result;
    }
    
    /**
     * 
     * @param day
     * @return
     */
    private int getDayOfWeek(Date day) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(day);
        int dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK);
        
        if(dayOfWeek == 1) {
            return 7;
        } else {
            return dayOfWeek -1;
        }
    }
    
    /**
     * 
     * @param courseSchedule
     * @return
     */
    public CourseScheduleCancelRecord loadScheduleCancelRecord(CourseSchedule courseSchedule) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("courseSchedule", courseSchedule);
        parameters.put("occurDate", courseSchedule.getOccurDate());
        
        return genericDaoSupport.searchForObject("FROM CourseScheduleCancelRecord WHERE courseSchedule = :courseSchedule AND occurDate = :occurDate", parameters, CourseScheduleCancelRecord.class);
    }
}
